import requests_html
from openpyxl import load_workbook
from tqdm import tqdm
import  csv
import requests
sourceFile='Item合集.xlsx'
wb=load_workbook(sourceFile)
sheet=wb['Sheet1']
sourceRows=sheet.max_row
sourceData=[]
for index in (range(sourceRows)):
    sourceData.append(sheet.cell(index+1,1).value)

sourceData=sourceData[1:]

#二维数组，为了能多行写入csv
toCsvList=[]
session = requests_html.HTMLSession()
for index_source,source in enumerate(tqdm(sourceData)):
    onedata=[]
    pageUrl = 'https://www.newegg.com/corsair-32gb-288-pin-ddr4-sdram/p/'+str(source)+'?Item='+str(source)
    web = session.get(url=pageUrl)
    htmlObj = web.html
    atc=htmlObj.xpath('//*[@id="ProductBuy"]/div/div[2]/button/text()', first=True)

    v2_Url='http://sandboxapis.newegg.org/api/product/v2/base'
    v2_Params={'ItemNumber':source,'IgnoreBuyBoxRanking':'false','CountryCode':'USA','languageCode':'en-US','CompanyCode':'1003','IsNeedEggPoint':'false'}
    v2_Headers={'Accept': 'application/json', 'Content-Type': 'application/json','Authorization':'Bearer Mk41kWzMchA0oSR7KIsFBu01EBLIpFtTzX7EJmNW'}
    r = requests.get(url=v2_Url, params=v2_Params, headers=v2_Headers)
    bestSeller=None
    if r.text!='' and r.status_code==200:
        if r.json().get("BestSeller")!=None:
            bestSeller = r.json().get("BestSeller")

    #cart后面确实是有空格的
    if atc=='Add to cart ' and bestSeller!=None:
        onedata.append(source)
    if onedata!=[]:
        toCsvList.append(onedata)



with open(sourceFile[:-4]+'csv','w',newline='')as file:
    op=csv.writer(file)
    op.writerows(toCsvList)




